using Dapper;
using Lzfy_Refund_Service.Services;
using Lzfy_Refund_Service.Models;
using Microsoft.Extensions.Logging;

namespace Lzfy_Refund_Service.Repositories
{
    /// <summary>
    /// 门诊患者就诊信息仓储实现类
    /// </summary>
    public class OutpPatientVisitRepository : IOutpPatientVisitRepository
    {
        private readonly IHisDGOracleConnectionFactory _hisDGOracleConnectionFactory;
        private readonly ILogger<OutpPatientVisitRepository> _logger;

        public OutpPatientVisitRepository(
            IHisDGOracleConnectionFactory hisDGOracleConnectionFactory,
            ILogger<OutpPatientVisitRepository> logger)
        {
            _hisDGOracleConnectionFactory = hisDGOracleConnectionFactory;
            _logger = logger;
        }

        /// <summary>
        /// 获取最近10分钟内的门诊患者就诊信息
        /// </summary>
        /// <returns>门诊患者就诊信息列表</returns>
        public async Task<IEnumerable<OutpPatientVisit>> GetRecentOutpPatientVisitsAsync()
        {
            try
            {
                using var connection = _hisDGOracleConnectionFactory.CreateConnection();
                
                const string sql = @"
                    SELECT T1.OUTP_VISIT_ID,
       T1.PATIENT_ID,
       T1.PATI_NAME,
       T1.OUTP_SPECIAL_NAME,
       T1.OUTP_DATE,
       T1.VISIT_ID,
       T1.ADMISSION_DOCTOR,
       -- 简化CASE表达式逻辑 
       CASE
         WHEN T2.QR_CODE LIKE '772%' THEN
          T2.QR_CODE
         WHEN T2.PATIENT_ID LIKE '9%' THEN
          (SELECT ';' || TB1.TRACK_DATA || '?' AS QRCODE
             FROM PCA.PCA_PATIENT_SERVICE_CARD_INFO TB1
             JOIN PCA.PCA_MEDIA_INFO TB2 -- 使用不同的别名避免冲突 
               ON TB1.CARD_NO = TB2.IDENTITY_NUMBER
            WHERE TB2.PATIENT_ID = T1.PATIENT_ID -- 明确关联条件 
              AND ROWNUM = 1)
         WHEN T2.PATIENT_ID LIKE '8%' THEN
          (SELECT ';' || TB3.MEDIA_UUID || '?' AS QRCODE
             FROM PCA.PCA_MEDIA_VERIFY_INFO TB3
            WHERE TB3.IDENTITY_NUMBER = T1.PATIENT_ID
              AND ROWNUM = 1)
         ELSE
          NULL -- 明确默认情况 
       END AS QR_CODE,
       CASE
         WHEN EXISTS
          (SELECT 1
                 FROM PTS.OUTP_PATIENT_VISIT TB4
                WHERE T1.PATIENT_ID = TB4.PATIENT_ID
                  AND T1.OUTP_SPECIAL_ID = TB4.OUTP_SPECIAL_ID
                  AND T1.OUTP_VISIT_ID != TB4.OUTP_VISIT_ID
                  AND TB4.REGISTRY_FLAG = 1
                  AND TB4.OUTP_DATE > SYSDATE - 4
                  AND TB4.VISIT_BEGIN_DATE < T1.VISIT_BEGIN_DATE
                  AND (T1.VISIT_BEGIN_DATE - TB4.VISIT_BEGIN_DATE) * 24 < 72) THEN
          '三日内就诊'
         ELSE
          '不免挂号费'
       END AS REGISTRATION_FEE_STATUS,
       T1.ADMISSION_DOCTOR || '&' || to_char(T1.OUTP_VISIT_ID) || '&' ||
       NVL(T3.PROPERTY_DESCN1, '透明') || '&' || 
       NVL(T3.PROPERTY_DESCN2, 'DarkViolet') || '&' ||
       TO_CHAR(NVL(T4.ID,0)) AS DOCTOR_VS_OUTP_VISIT_ID,
       NVL(T3.ITEM_NAME, '正常') AS PAT_LEVEL,
       T4.LIS_COUNT,
       T4.EXAM_COUNT,
       '0' as READ_FLAG,
       COALESCE((SELECT LISTAGG(TB2.ORDER_NAME || ' ' ||
                               DECODE(NVL(TB2.EXEC_FEE_STATUS, '0'),
                                      '0',
                                      '未计费',
                                      '1',
                                      '已计费'),
                               ',') WITHIN GROUP(ORDER BY TB2.ORDER_NAME || ' ' || DECODE(NVL(TB2.EXEC_FEE_STATUS, '0'), '0', '未计费', '1', '已计费'))
                  FROM EMR.CPOE_ORD TB2
                 WHERE T1.PATIENT_ID = TB2.PATIENT_ID
                   AND T1.OUTP_VISIT_ID = TB2.PATIENT_VISIT_ID
                   AND TB2.ORDER_SOURCE = '1'
                   AND TB2.ORDER_NAME LIKE '%诊查费%'),
                '未开诊查费') AS ORDER_NAMES
  FROM PTS.OUTP_PATIENT_VISIT T1
  JOIN PCA.PCA_PATIENT_INFO T2
    ON T1.PATIENT_ID = T2.PATIENT_ID
  LEFT JOIN BDS.BDS_CODE_TABLE_ITEM T3
    ON T2.LEVEL_FLAG = T3.ITEM_VALUE
   AND T3.T_ID = 173332
  LEFT JOIN PTS.OUTP_VISIT_LOGS T4
    ON T1.OUTP_VISIT_ID = T4.OUTP_VISIT_ID
 WHERE T1.VISIT_BEGIN_DATE > SYSDATE - 1 / 144 -- 10分钟内的数据 
   AND T1.REGISTRY_FLAG = 1";

                var result = await connection.QueryAsync<OutpPatientVisit>(sql);
                
                _logger.LogInformation("查询到 {Count} 条门诊患者就诊信息", result.Count());
                _logger.LogInformation("执行的SQL查询: {Sql}", sql);
                
                // 记录强类型映射后的结果
                foreach (var item in result.Take(2)) // 只记录前2条
                {
                    _logger.LogInformation("强类型映射结果 - OUTP_VISIT_ID: '{OUTP_VISIT_ID}', PATIENT_ID: '{PATIENT_ID}', PATI_NAME: '{PATI_NAME}', DOCTOR_VS_OUTP_VISIT_ID: '{DOCTOR_VS_OUTP_VISIT_ID}'", 
                        item.OUTP_VISIT_ID, item.PATIENT_ID, item.PATI_NAME, item.DOCTOR_VS_OUTP_VISIT_ID);
                }
                
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询门诊患者就诊信息失败！");
                throw;
            }
        }
    }
}